from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationships,session
from sqlalchemy import Column,Integer,String,Date,ForeignKey
Base = declarative_base()

class Delivery(Base):
    __tablename__='delivery'
    dno = Column(String(10),primary_key=True)
    dev_date = Column(Date)
    num = Column(Integer)
    city = Column(String(10))


class Orders(Base):
    __tablename__= 'orders'
    oid = Column(String(10),primary_key=True)
    name = Column(String(20),nullable=False)
    ord_date = Column(Date,nullable=False)
    number = Column(Integer,nullable=False)
    dno = Column(String(20),ForeignKey(Delivery.dno))


class myOrm:
    def __init__(self, username, password, host, port, dbname):
        sql = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(username,password,host,port,dbname)
        engine = create_engine(sql,max_overflow=5)
        Session = sessionmaker(bind=engine)#创建一个sessionmaker对象
        self.session = Session()

    # def __inint__(self):
    #     self.engine = create_engine('mysql+pymysql://root:123456@localhost:3306/mydb?charset=utf8',max_overflow=5)
    #     maker = sessionmaker(bind=self.engine)
    #     self.session = maker()

    def add_obj(self,obj):
        self.session.add(obj)
        self.session.commit()

    def add_objs(self,ls):
        self.session.add_all(ls)
        self.session.commit()

    def remove_obj(self,obj):
        self.session.query(Orders).filter_by(oid=obj.oid).delete()
        self.session.commit()

    def update_obj(self,obj):
        self.session.query(Orders).filter_by(oid=obj.oid).update({'name':obj.name,'ord_date':obj.ord_date,'number':obj.number})
        self.session.commit()

    def findById(self,id):
        return self.session.query(Orders).filter_by(oid=id).first()

    def findAll(self):
        return self.session.query(Orders).all()

    def seacher(self):
        # query = self.session.query(Orders,Delivery).filter(Orders.dno==Delivery.dno).all()
        query = self.session.query(Orders.oid,Orders.name,Delivery.city).join(Delivery).all()
        #join 连接  需要外键设置
        print(query)
        return query
        # self.session.query(Orders).join(Delivery,isouter=True).all()#外连接 默认左连接


if __name__ == '__main__':
    order = Orders(oid='10011',name='tv',ord_date='2018-10-01',number=3,dno='1111')
    deliver = Delivery(dno='1111',dev_date='2018-09-02',num=5,city='zhengzhou')
    orm = myOrm('root', '123456', 'localhost', 3306, 'mydb')
    # orm = myOrm()
    # orm.add_obj(order)
    # orm.add_obj(deliver)
    orm.seacher()